﻿

using System;
using AutoMapper;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using System.Collections.Generic;
using Microsoft.Extensions.Logging;
using Microsoft.AspNetCore.Authorization;

using Oliving.Edge.AuthHelper;
using Oliving.Edge.Model;
using Oliving.Edge.Model.Models;
using Oliving.Edge.IServices;
using Oliving.Edge.Model.Dto;
using Oliving.Edge.IServices.DTSN;
using Oliving.Edge.Model.Dto.DTSN;
using Oliving.Edge.Model.DTSN;
using System.Linq.Expressions;
using Oliving.Edge.Common.Extensions;
using System.IO;
using OfficeOpenXml;
using Microsoft.AspNetCore.Hosting;
using Oliving.Edge.Common.Document;
using System.Data;

namespace Oliving.Edge.Controllers
{
    [ApiController]
    [Route("api/DTSN_CanningRecord")]
    [Authorize(Permissions.Name)]
    public class DTSN_CanningRecordController : OlivingControll
    {
        private IHostingEnvironment _hostingEnvironment;
        #region 初始化

        IMapper _mapper;
        private readonly IDTSN_CanningRecordServices _DTSN_CanningRecordServices;
        private readonly ILogger<DTSN_CanningRecordController> _logger;


        public DTSN_CanningRecordController(
                IHttpContextAccessor accessor, PermissionRequirement requirement,
                IMapper mapper, ILogger<DTSN_CanningRecordController> logger,
                IDTSN_CanningRecordServices DTSN_CanningRecordServices,
                IHostingEnvironment hostingEnvironment
            ) : base(accessor, requirement)
        {
            _logger = logger;
            _mapper = mapper;
            _DTSN_CanningRecordServices = DTSN_CanningRecordServices;
            _hostingEnvironment = hostingEnvironment;
        }


        #endregion



        #region 增删改查



        [HttpGet]
        [AllowAnonymous]
        [Route("/api/[controller]/Get")]
        public async Task<MessageModel<DTSN_CanningRecordDto>> Get(int id)
        {
            var data = new MessageModel<DTSN_CanningRecordDto>();
            DTSN_CanningRecord model = await _DTSN_CanningRecordServices.QueryById(id);
            if (model == null)
            {
                data.response = null;
                data.msg = "数据不存在！";
                data.success = false;
            }
            else
            {
                var dto = _mapper.Map<DTSN_CanningRecordDto>(model);
                data.response = dto;
                data.msg = "获取成功！";
                data.success = true;
            }
            return data;
        }




        [HttpGet]
        [Route("/api/[controller]/GetPageList")]
        public async Task<MessageModel<PageModel<DTSN_CanningRecordPageDto>>> GetPageList(
            int page = 1, int PageSize = 20, string sidx = "", string order = "",
            string key = "",
            string begin = "", string end = "",
            string tongDao = "", int type = 0
            )
        {
            Expression<Func<DTSN_CanningRecord, bool>> whereExpression = _DTSN_CanningRecord => true;

            #region MyRegion

            if (string.IsNullOrEmpty(key) || string.IsNullOrWhiteSpace(key))
            {
                key = "";
            }
            else
            {
                whereExpression = whereExpression.And(d =>
                d.CR_Car.Contains(key)
                || d.CR_CardCode.Contains(key)
                || d.CR_TongDaoCode.Contains(key)
                || d.CR_Varieties.Contains(key)
                || d.CR_weightserialcode.Contains(key)
                || d.CR_accountcode.Contains(key)
                || d.CR_accountname.Contains(key)
                    );
            }

            DateTime beginDay = DateTime.MinValue;
            DateTime.TryParse(begin, out beginDay);

            DateTime endDay = DateTime.MinValue;
            DateTime.TryParse(end, out endDay);

            if (!string.IsNullOrEmpty(begin) && beginDay > DateTime.MinValue)
            {
                whereExpression = whereExpression.And(d => d.CR_BeginTime >= beginDay);
            }

            if (!string.IsNullOrEmpty(end) && endDay > DateTime.MinValue)
            {
                whereExpression = whereExpression.And(d => d.CR_BeginTime <= endDay);
            }

            if (!string.IsNullOrEmpty(tongDao))
            {
                whereExpression = whereExpression.And(d => d.CR_TongDaoCode == tongDao);
            }

            if (type > 0)
            {
                whereExpression = whereExpression.And(d => d.CR_Type == type);
            }


            #endregion


            var data = await _DTSN_CanningRecordServices.QueryPageList(whereExpression, page, PageSize, sidx, order);

            return new MessageModel<PageModel<DTSN_CanningRecordPageDto>>()
            {
                msg = "获取成功",
                success = true,
                response = data
            };
        }



        [HttpPut]
        [Route("/api/[controller]/Update")]
        public async Task<MessageModel<string>> Update([FromBody] DTSN_CanningRecordDto dto)
        {
            var data = new MessageModel<string>();

            if (dto != null && dto.CR_ID > 0)
            {
                DTSN_CanningRecord model = await _DTSN_CanningRecordServices.QueryById(dto.CR_ID);
                if (model != null)
                {
                    model.CR_Type = dto.CR_Type;
                    model.CR_Car = dto.CR_Car;
                    model.CR_CardCode = dto.CR_CardCode;
                    model.CR_truckweight = dto.CR_truckweight;
                    model.CR_TongDaoCode = dto.CR_TongDaoCode;
                    model.CR_Varieties = dto.CR_Varieties;
                    model.CR_Order = dto.CR_Order;
                    model.CR_weightserialcode = dto.CR_weightserialcode;
                    model.CR_accountcode = dto.CR_accountcode;
                    model.CR_accountname = dto.CR_accountname;
                    model.CR_Date = dto.CR_Date;
                    model.CR_BeginTime = dto.CR_BeginTime;
                    model.CR_EndTime = dto.CR_EndTime;
                    model.CR_Orderpreweight = dto.CR_Orderpreweight ?? 0;
                    model.CR_LoadingCapacity = dto.CR_LoadingCapacity ?? 0;


                    data.success = await _DTSN_CanningRecordServices.Update(model);

                    if (data.success)
                    {
                        data.msg = "更新成功";
                        data.response = dto.CR_ID.ObjToString();
                    }
                }
                else
                {
                    data.success = false;
                    data.msg = "参数异常，您要更新的数据不存在";
                }
            }
            else
            {
                data.success = false;
                data.msg = "参数异常";
            }
            return data;
        }





        [HttpPost]
        [Route("/api/[controller]/Add")]
        public async Task<MessageModel<string>> Add(DTSN_CanningRecord dto)
        {
            var data = new MessageModel<string>();
            int id = await _DTSN_CanningRecordServices.Add(dto);

            data.success = id > 0;
            if (data.success)
            {
                data.response = id.ObjToString();
                data.msg = "添加成功";
            }
            return data;
        }




        [HttpDelete]
        [Route("/api/[controller]/Delete")]
        public async Task<MessageModel<string>> Delete(int id)
        {
            var data = new MessageModel<string>();
            if (id > 0)
            {
                var userDetail = await _DTSN_CanningRecordServices.QueryById(id);
                // userDetail.C_State = -1; ?????????????
                data.success = await _DTSN_CanningRecordServices.Update(userDetail);
                if (data.success)
                {
                    data.msg = "删除成功";
                    data.response = userDetail?.CR_ID.ObjToString();
                }
            }
            return data;
        }





        [HttpDelete]
        [Route("/api/[controller]/BatchDelete")]
        public async Task<MessageModel<string>> BatchDelete(string ids)
        {
            var data = new MessageModel<string>();
            if (!string.IsNullOrEmpty(ids))
            {
                if (false)
                {
                    #region 软删除

                    int[] list = Array.ConvertAll<string, int>(ids.Split(','), s => int.Parse(s));

                    DTSN_CanningRecord model = new DTSN_CanningRecord();
                    //  model.C_State = -1;          ???????????????????
                    string whereStr = " CR_ID in (" + ids + ")";
                    List<string> colList = new List<string>();
                    //  colList.Add("C_State");    ???????????????????
                    List<string> colHL = new List<string>();

                    bool isOK = await _DTSN_CanningRecordServices.Update(model, colList, colHL, whereStr);

                    data.success = isOK;
                    if (data.success)
                    {
                        data.msg = "删除成功";
                        data.response = list.Count().ObjToString();
                    }

                    #endregion
                }
                {
                    #region 直接删除 

                    object[] list = Array.ConvertAll<string, object>(ids.Split(','), s => int.Parse(s));

                    bool isOK = await _DTSN_CanningRecordServices.DeleteByIds(list);

                    data.success = isOK;
                    if (data.success)
                    {
                        data.msg = "删除成功";
                        data.response = list.Count().ObjToString();
                    }
                    #endregion
                }
            }
            return data;
        }


        #endregion


        #region 导出


        /// <summary>
        /// 不根据指定模板进行导出excel下载(非npoi实现)
        /// </summary>
        /// <returns></returns>
        /// 
        [HttpGet]
        [AllowAnonymous]
        [Route("/api/[controller]/Export2")]
        public async Task<IActionResult> Export2(
            string begin = "", string end = "",
            string tongDao = "", int type = 0)
        {
            #region file Init

            string sWebRootFolder = _hostingEnvironment.WebRootPath + "//Excel//";
            string sFileName = $"装载记录_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";

            //  ((Microsoft.AspNetCore.Http.DefaultHttpRequest)this.Request).Host

            string httpPath = this.Request.Host.ToString() + @"\Excel\" + sFileName;

            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            file.Delete();

            #endregion

            #region 查询

            Expression<Func<DTSN_CanningRecord, bool>> whereExpression = _DTSN_CanningRecord => true;

            DateTime beginDay = DateTime.MinValue;
            DateTime.TryParse(begin, out beginDay);

            DateTime endDay = DateTime.MinValue;
            DateTime.TryParse(end, out endDay);


            if (!string.IsNullOrEmpty(begin) && beginDay > DateTime.MinValue)
            {
                whereExpression = whereExpression.And(d => d.CR_BeginTime >= beginDay);
            }

            if (!string.IsNullOrEmpty(end) && endDay > DateTime.MinValue)
            {
                whereExpression = whereExpression.And(d => d.CR_BeginTime <= endDay);
            }

            if (!string.IsNullOrEmpty(tongDao))
            {
                whereExpression = whereExpression.And(d => d.CR_TongDaoCode == tongDao);
            }

            if (type > 0)
            {
                whereExpression = whereExpression.And(d => d.CR_Type == type);
            }


            var data = await _DTSN_CanningRecordServices.QueryPageList(whereExpression, 1, 9999, "", "");


            #endregion

            using (ExcelPackage package = new ExcelPackage(file))
            {
                #region MyRegion

                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("装载记录");
                //添加头
                worksheet.Cells[1, 1].Value = "开始时间";
                worksheet.Cells[1, 2].Value = "结束时间";
                worksheet.Cells[1, 3].Value = "物料种类";
                worksheet.Cells[1, 4].Value = "车牌号";
                worksheet.Cells[1, 5].Value = "车辆标载";
                worksheet.Cells[1, 6].Value = "通道代码";
                worksheet.Cells[1, 7].Value = "品种";
                worksheet.Cells[1, 8].Value = "订单号";
                worksheet.Cells[1, 9].Value = "客户名称";
                worksheet.Cells[1, 10].Value = "请发凉";
                worksheet.Cells[1, 11].Value = "装载量";

                #endregion

                int i = 1;
                foreach (DTSN_CanningRecordPageDto item in data.data)
                {
                    i = i + 1;
                    //添加值
                    worksheet.Cells["A" + i].Value = Convert.ToDateTime(item.CR_BeginTime).ToString("yyyy-MM-dd HH:mm:ss");
                    worksheet.Cells["B" + i].Value = Convert.ToDateTime(item.CR_EndTime).ToString("yyyy-MM-dd HH:mm:ss");
                    worksheet.Cells["C" + i].Value = item.CR_Type == 1 ? "熟料" : "散料";


                    worksheet.Cells["D" + i].Value = item.CR_Car;
                    worksheet.Cells["E" + i].Value = item.CR_truckweight;
                    worksheet.Cells["F" + i].Value = item.CR_TongDaoCode;
                    worksheet.Cells["G" + i].Value = item.CR_Varieties;
                    worksheet.Cells["H" + i].Value = item.CR_Order;

                    worksheet.Cells["I" + i].Value = item.CR_accountname;
                    worksheet.Cells["J" + i].Value = item.CR_Orderpreweight;
                    worksheet.Cells["K" + i].Value = item.CR_LoadingCapacity;

                }
                //worksheet.Cells["A3"].Value = 1001;
                //worksheet.Cells["B3"].Value = "For丨丶Tomorrow";
                //worksheet.Cells["C3"].Value = "https://buluo.qq.com/p/barindex.html?bid=310072";
                //worksheet.Cells["C3"].Style.Font.Bold = true;



                package.Save();
            }
            return File(@"\Excel\" + sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
        }




        [HttpGet]
        [AllowAnonymous]
        [Route("/api/[controller]/ExportURL")]
        public async Task<MessageModel<string>> ExportURL(
       string begin = "", string end = "",
       string tongDao = "", int type = 0)
        {
            #region file Init httpPath

            string sWebRootFolder = _hostingEnvironment.WebRootPath + "//Excel//";
            string sFileName = $"装载记录_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";

            //  ((Microsoft.AspNetCore.Http.DefaultHttpRequest)this.Request).Host

            string httpPath = this.Request.Scheme + "://" + this.Request.Host.ToString() + "/Excel/" + sFileName;

            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            file.Delete();

            #endregion


            #region 查询

            Expression<Func<DTSN_CanningRecord, bool>> whereExpression = _DTSN_CanningRecord => true;

            DateTime beginDay = DateTime.MinValue;
            DateTime.TryParse(begin, out beginDay);

            DateTime endDay = DateTime.MinValue;
            DateTime.TryParse(end, out endDay);


            if (!string.IsNullOrEmpty(begin) && beginDay > DateTime.MinValue)
            {
                whereExpression = whereExpression.And(d => d.CR_BeginTime >= beginDay);
            }

            if (!string.IsNullOrEmpty(end) && endDay > DateTime.MinValue)
            {
                whereExpression = whereExpression.And(d => d.CR_BeginTime <= endDay);
            }

            if (!string.IsNullOrEmpty(tongDao))
            {
                whereExpression = whereExpression.And(d => d.CR_TongDaoCode == tongDao);
            }

            if (type > 0)
            {
                whereExpression = whereExpression.And(d => d.CR_Type == type);
            }


            var data = await _DTSN_CanningRecordServices.QueryPageList(whereExpression, 1, 9999, "", "");


            #endregion

            using (ExcelPackage package = new ExcelPackage(file))
            {
                #region MyRegion

                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("装载记录");
                //添加头
                worksheet.Cells[1, 1].Value = "开始时间";
                worksheet.Cells[1, 2].Value = "结束时间";
                worksheet.Cells[1, 3].Value = "物料种类";
                worksheet.Cells[1, 4].Value = "车牌号";
                worksheet.Cells[1, 5].Value = "车辆标载";
                worksheet.Cells[1, 6].Value = "通道代码";
                worksheet.Cells[1, 7].Value = "品种";
                worksheet.Cells[1, 8].Value = "订单号";
                worksheet.Cells[1, 9].Value = "客户名称";
                worksheet.Cells[1, 10].Value = "请发凉";
                worksheet.Cells[1, 11].Value = "装载量";

                #endregion

                int i = 1;
                foreach (DTSN_CanningRecordPageDto item in data.data)
                {
                    i = i + 1;
                    //添加值
                    worksheet.Cells["A" + i].Value = Convert.ToDateTime(item.CR_BeginTime).ToString("yyyy-MM-dd HH:mm:ss");
                    worksheet.Cells["B" + i].Value = Convert.ToDateTime(item.CR_EndTime).ToString("yyyy-MM-dd HH:mm:ss");
                    worksheet.Cells["C" + i].Value = item.CR_Type == 1 ? "熟料" : "散料";


                    worksheet.Cells["D" + i].Value = item.CR_Car;
                    worksheet.Cells["E" + i].Value = item.CR_truckweight;
                    worksheet.Cells["F" + i].Value = item.CR_TongDaoCode;
                    worksheet.Cells["G" + i].Value = item.CR_Varieties;
                    worksheet.Cells["H" + i].Value = item.CR_Order;

                    worksheet.Cells["I" + i].Value = item.CR_accountname;
                    worksheet.Cells["J" + i].Value = item.CR_Orderpreweight;
                    worksheet.Cells["K" + i].Value = item.CR_LoadingCapacity;

                }
                //worksheet.Cells["A3"].Value = 1001;
                //worksheet.Cells["B3"].Value = "For丨丶Tomorrow";
                //worksheet.Cells["C3"].Value = "https://buluo.qq.com/p/barindex.html?bid=310072";
                //worksheet.Cells["C3"].Style.Font.Bold = true;



                package.Save();
            }
            return new MessageModel<string>
            {
                msg = "请按地址下载文件",
                response = httpPath,
                success = true
            };
        }







        /// <summary>
        /// 测试未通过
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        [Route("/api/[controller]/Export")]
        public IActionResult Export()
        {
            var TempletFilePath = @"D:\谷歌下载\ASPNETCore操作Excel\ASPNETCoreExcel\Template";//模板地址
            var ExportFilePath = "D:";//导出后存放的地址           
            string TempletFileName = string.Format("{0}\\调查表.xls", TempletFilePath);
            string ExportFileName = string.Format("{0}\\调查表_{1}.xls", ExportFilePath, DateTime.Now.ToString("yyyy年MM月dd日hh时mm分ss秒"));

            var _NpoiExcelUtility = new NpoiExcelUtility(ExportFileName, TempletFileName);
            #region 测试数据
            //创建DataTable
            DataTable dt = new DataTable("NewDt");

            //创建自增长的ID列
            DataColumn dc = dt.Columns.Add("id", Type.GetType("System.Int32"));
            dc.AutoIncrement = true;   //自动增加
            dc.AutoIncrementSeed = 1;  //起始为1
            dc.AutoIncrementStep = 1;  //步长为1
            dc.AllowDBNull = false;    //非空

            //创建其它列表
            dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));

            //创建数据
            DataRow dr = dt.NewRow();
            dr["Name"] = "张三";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["Name"] = "李四";
            dt.Rows.Add(dr);
            dr = dt.NewRow();
            dr["Name"] = "王五";
            dt.Rows.Add(dr);
            #endregion
            _NpoiExcelUtility.CreatExcelSheet("调查表", dt);
            _NpoiExcelUtility.SaveExcel();
            var stream = System.IO.File.OpenRead(ExportFileName);//excel表转换成流
            return File(stream, "application/vnd.android.package-archive", Path.GetFileName(ExportFileName));//进行浏览器下载

        }



        #endregion



    }
}

